using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using DTO;
namespace DAO
{
  public   class SachDAO
    {
      public static DataTable laydanhsachsach_DAO()
      {
          DataTable dt = new DataTable();
          string sql = "select * from Sach";
          dt = DataProvider.ExcuteQuery(sql);
          return dt;
      }

      public static DataTable LayTatCaSach_DAO()
      {
          DataTable dt = new DataTable();
          string sql=" SELECT Sach.MaSach, Sach.TenSach , LoaiSach.TenLoaiSach,";
          sql += " Sach.TacGia, Sach.NamXuatBan, Sach.NhaXuatBan, Sach.NgayNhap,";
          sql += " Sach.Gia, NhanVien.HoTen,     Sach.TinhTrang ";
          sql += " FROM ((Sach INNER JOIN ";
          sql += " NhanVien ON Sach.NguoiTiepNhan = NhanVien.MaNhanVien) INNER JOIN ";
          sql += "  LoaiSach ON Sach.TheLoai = LoaiSach.MaTheLoaiSach) ";
          dt = DataProvider.ExcuteQuery(sql);
          return dt;

      }

      public static DataTable TimKiemSach_DAO(string tenSach)
      {

          DataTable dt = new DataTable();
          string sql = " SELECT Sach.MaSach, Sach.TenSach , LoaiSach.TenLoaiSach,";
          sql += " Sach.TacGia, Sach.NamXuatBan, Sach.NhaXuatBan, Sach.NgayNhap,";
          sql += " Sach.Gia, NhanVien.HoTen,     Sach.TinhTrang,Sach.TheLoai ";
          sql += " FROM ((Sach INNER JOIN ";
          sql += " NhanVien ON Sach.NguoiTiepNhan = NhanVien.MaNhanVien) INNER JOIN ";
          sql += "  LoaiSach ON Sach.TheLoai = LoaiSach.MaTheLoaiSach) where Sach.TenSach like '%"+tenSach+"%'";
          dt = DataProvider.ExcuteQuery(sql);
          return dt;
      }

      public static DataTable TimKiemSachNangCao_DAO(string param)
      {
          DataTable dt = new DataTable();
          string sql = " SELECT Sach.MaSach, Sach.TenSach , LoaiSach.TenLoaiSach,";
          sql += " Sach.TacGia, Sach.NamXuatBan, Sach.NhaXuatBan, Sach.NgayNhap,";
          sql += " Sach.Gia, NhanVien.HoTen,     Sach.TinhTrang,Sach.TheLoai";
          sql += " FROM ((Sach INNER JOIN ";
          sql += " NhanVien ON Sach.NguoiTiepNhan = NhanVien.MaNhanVien) INNER JOIN ";
          sql += "  LoaiSach ON Sach.TheLoai = LoaiSach.MaTheLoaiSach) where 1 ";
          if(param.Length>0)
              sql+= " " + param;
          //Sach.TenSach like '%" + tenSach + "%'";
          dt = DataProvider.ExcuteQuery(sql);
          return dt;
      }

      public static void XoaSach_DAO(int maSach)
      {
          string sql = "Delete from Sach where MaSach=" + maSach.ToString();
          DataProvider.ExecutenonQuery(sql);
      }

      public static void CapNhatSach_DAO(SachDTO sachDTO)
      {
          string sql = "update Sach set ";
          sql += "TenSach='" + sachDTO.Tensach + "',TheLoai=" + sachDTO.Theloai.ToString() + ",TacGia='" + sachDTO.Tacgia.ToString() + "',NamXuatBan=" + sachDTO.Namxuatban.ToString() + ",NhaXuatBan='" + sachDTO.Nhaxuatban + "',NgayNhap='" + sachDTO.Ngaynhap.ToString() + "',Gia=" + sachDTO.Gia.ToString() + ",NguoiTiepNhan=" + sachDTO.Nguoitiepnhan.ToString() + ",TinhTrang= "+sachDTO.Tinhtrang.ToString();
          sql += " where MaSach=" + sachDTO.Masach.ToString();
          DataProvider.ExecutenonQuery(sql);
    
      }
    }
}
